﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;


namespace Models
{
    public class AdminPanelDataAccess : DataAccessBase
    {
        #region Creation

        public AdminPanelDataAccess(DataAccessConnection state = DataAccessConnection.Closed)
            : base(state)
        {

        }

        #endregion

        #region Brands

        public int CountBrands()
        {
            command.Parameters.Clear();
            command.CommandText = "SP_GetBrandCount";
            return (int)command.ExecuteScalar();
        }

        public List<Brand> AllBrands()
        {
            var list = new List<Brand>();
            command.Parameters.Clear();
            command.CommandText = "SP_GetAllBrands";

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var temp = new Brand((int)reader["Id"], (string)reader["Name"]);
                    list.Add(temp);
                }
            }

            return list;
        }

        #endregion

        #region Categories

        public int CountCategories()
        {
            command.Parameters.Clear();
            command.CommandText = "SP_GetCategoryCount";
            return (int)command.ExecuteScalar();
        }

        public List<Category> AllCategories()
        {
            var list = new List<Category>();
            command.Parameters.Clear();
            command.CommandText = "SP_GetAllCategories";

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var temp = new Category((int)reader["Id"], (string)reader["Name"]);
                    list.Add(temp);
                }
            }

            return list;
        }

        #endregion

        #region Generic Items

        public int CountGenericItems()
        {
            command.Parameters.Clear();
            command.CommandText = "SP_GetGenericItemCount";
            return (int)command.ExecuteScalar();
        }

        public List<GenericItem> AllGenericItems(int categoryId, int brandId)
        {
            var list = new List<GenericItem>();
            command.Parameters.Clear();
            command.CommandText = "SP_GetGenericItemsFromBrandCategory";

            command.Parameters.AddWithValue("@CategoryId", categoryId);
            command.Parameters.AddWithValue("@BrandId", brandId);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var temp = new GenericItem((int)reader["Id"], (string)reader["Name"], (decimal)reader["Price"], (bool)reader["Available"]);
                    list.Add(temp);
                }
            }

            return list;
        }

        public List<Item> ItemsFromGenericItemId(int genericItemId)
        {
            List<Item> list = new List<Item>();
            command.Parameters.Clear();
            command.CommandText = "SP_GetItemsFromGenericItemId";

            command.Parameters.AddWithValue("@GenericItemId", genericItemId);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Item temp = new Item((int)reader["Id"], (int)reader["Quantity"], (bool)reader["Available"], (string)reader["Color"], (string)reader["Size"]);
                    list.Add(temp);
                }
            }

            return list;
        }



        #endregion

        #region Items

        public int GetColorIdByItem(int itemId)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_GetItemDetails";
            command.Parameters.AddWithValue("@Id", itemId);

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    return (int)reader["ColorId"];
                }
            }
            return 0;
        }

        #endregion

        #region Images

        public ImageData GetImage(int genericItemId, int colorId)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_GetImages";
            command.Parameters.AddWithValue("@GenericItemId", genericItemId);
            command.Parameters.AddWithValue("@ColorId", colorId);

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        return new ImageData(
                            id: (int)reader["Id"],
                            original: (byte[])reader["Original"],
                            resized: (byte[])reader["Resized"]
                        );
                    }
                }
            }
            return null;
        }

        public int ImageExists(int genericItemId, int colorId)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_ImageExists";
            command.Parameters.AddWithValue("@GenericItemId", genericItemId);
            command.Parameters.AddWithValue("@ColorId", colorId);
            return Convert.ToInt32(command.ExecuteScalar());
        }

        public int InsertImage(byte[] original, byte[] resized)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_SaveImage";

            SqlParameter param = command.Parameters.Add("@Original", SqlDbType.VarBinary, 5242880);
            param.Value = original;

            param = command.Parameters.Add("@Resized", SqlDbType.VarBinary, 5242880);
            param.Value = resized;

            return Convert.ToInt32(command.ExecuteScalar());
        }

        public void InsertItemPicture(int genericItemId, int colorId, int pictureId)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_InsertItemPicture";
            command.Parameters.AddWithValue("@GenericItemId", genericItemId);
            command.Parameters.AddWithValue("@ColorId", colorId);
            command.Parameters.AddWithValue("@PictureId", pictureId);
            command.ExecuteNonQuery();
        }

        public ImageData GetImageById(int id)
        {
            connection.Open();
            command.Parameters.Clear();
            command.CommandText = "SP_GetImageById";
            command.Parameters.AddWithValue("@Id", id);

            ImageData img = null;

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    img = new ImageData(
                        id: (int)reader["Id"],
                        original: (byte[])reader["Original"],
                        resized: (byte[])reader["Resized"]
                    );
                }
            }
            connection.Close();
            return img;
        }

        #endregion

        #region GenericItemDescription

        public ItemDescription SelectItemDescription(int genericItemId)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_SelectItemDescription";
            command.Parameters.AddWithValue("@GenericItemId", genericItemId);

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        string description = (reader["ItemDescription"] != DBNull.Value) ? (string)(reader["ItemDescription"]) : "";
                        string displayName = (reader["DisplayName"] != DBNull.Value) ? (string)(reader["DisplayName"]) : "";
                        string material = (reader["Material"] != DBNull.Value) ? (string)(reader["Material"]) : "";
                        decimal discountPrice = (reader["DiscountPrice"] != DBNull.Value) ? (decimal)(reader["DiscountPrice"]) : 0;

                        return new ItemDescription(
                            genericItemId: (int)reader["GenericItemId"],
                            itemDescription: description,
                            material: material,
                            discountPrice: discountPrice,
                            displayName: displayName
                        );
                    }
                }
            }
            return null;

        }

        public void UpdateItemDescription(int genericItemId, string itemDescription, string material, decimal discountPrice, string displayName)
        {
            command.Parameters.Clear();
            command.CommandText = "SP_UpdateItemDescription";
            command.Parameters.AddWithValue("@GenericItemId", genericItemId);
            command.Parameters.AddWithValue("@ItemDescription", itemDescription);
            command.Parameters.AddWithValue("@Material", material);
            command.Parameters.AddWithValue("@DiscountPrice", discountPrice);
            command.Parameters.AddWithValue("@DisplayName", displayName);

            command.ExecuteNonQuery();
        }

        #endregion
    }
}
